package com.ctsi.ssdc.utils;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.jetbrains.annotations.Nullable;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Objects;
import java.util.regex.Pattern;

public class ExcelUtils {

    public static <T> List<T> importExcel(Workbook wb, String sheetName, int startReadLine, Date date,Class<T> aClass,ImportExcel<T> importExcel) {
        int sheetIndex = getSheetIndex(wb, sheetName);
        //读取excel表中的sheet, 参数为sheet的索引值（从0开始）
        Sheet sheet = wb.getSheetAt(sheetIndex);
        Row row = null;
        //获取该sheet的最后一行数据的索引
        int lastRowNum = sheet.getLastRowNum();
        // list
        List<T> oList = new ArrayList<>();
        //外循环是循环行，内循环是循环每行的单元格
        for (int i = startReadLine; i <= lastRowNum; i++) {
            T o = null;
            try {
                o = aClass.newInstance();
            } catch (InstantiationException e) {
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }
            //根据行索引获取行对象（单元格集合）
            row = sheet.getRow(i);
            // 空指针直接向下
            if (row == null) continue;
            int count = 0; // 用于记录空白元素个数 3个时直接break
            boolean isEmpty = true;
            //遍历行的单元格，并解析
            for (Cell c : row) {
                String returnStr = "";
                String trim = "";
                //设置该单元格的数据的类型为String
                c.setCellType(Cell.CELL_TYPE_STRING);
                boolean isMerge = ExcelUtils.isMergedRegion(sheet, i, c.getColumnIndex());
                // 判断是否具有合并单元格
                if (isMerge) {
                    //如果是合并单元格，就获取合并单元格的值
                    returnStr = ExcelUtils.getMergedRegionValue(sheet, row.getRowNum(), c.getColumnIndex()).toString();
                } else {
                    //不是合并单元格直接获取单元格的值
                    returnStr = ExcelUtils.getCellValue(c).toString();
                }
                //当遍历到空白列时 第4次跳出循环
                if (StringUtils.isEmpty(returnStr)) {
                    if (count++ == 3) {
                        break;
                    }
                }
                if (Objects.nonNull(returnStr) && StringUtils.isNotEmpty(returnStr)) {
                    trim = returnStr.trim();
                    trim = trim.trim();
                    // 如果是合计
                    if (trim.contains("合计")) {
                        // 直接跳过
                        o = null;
                        break;
                    }
                    //封装结果集，一行数据封装为一个对象
                    T res = importExcel.getArrayObject(c.getColumnIndex(), o, trim);
                    if (res != null) {
                        isEmpty = false;
                        o = res;
                    }
                }

            }
            //判断一个对象的属性是否都为空， true:都为空  ， false: 不都为空
//            if (!ExcelUtils.allFieldIsNULL(o)) {
            if (o != null && !isEmpty) {
                try {
                    Method time = aClass.getDeclaredMethod("setStatisticalTime", Date.class);
                    time.invoke(o,date);
                } catch (Exception e) {
                    e.printStackTrace();
                }
                //该对象不都为空的情况下，添加到集合中
                oList.add(o);
            }
        }

        return oList;
    }

    @Nullable
    public static Workbook getWorkbook(MultipartFile uploadExcel) {
        Workbook wb = null;
        try {
            wb = WorkbookFactory.create(uploadExcel.getInputStream());
        } catch (IOException e) {
            e.printStackTrace();
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        }
        return wb;
    }


    /**
     * 判断指定的单元格是否是合并单元格
     *
     * @param sheet
     * @param row    行下标
     * @param column 列下标
     * @return
     */
    public static boolean isMergedRegion(Sheet sheet, int row, int column) {
        //获取该sheet所有合并的单元格
        int sheetMergeCount = sheet.getNumMergedRegions();
        //循环判断 该单元格属于哪个合并单元格， 如果能找到对应的，就表示该单元格是合并单元格
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    return true;
                }
            }
        }
        return false;
    }

    /**
     * 获取合并单元格的值
     *
     * @param sheet  sheet索引 从0开始
     * @param row    行索引 从0开始
     * @param column 列索引  从0开始
     * @return
     */
    public static String getMergedRegionValue(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    Row fRow = sheet.getRow(firstRow);
                    Cell fCell = fRow.getCell(firstColumn);
                    return getCellValue(fCell);
                }
            }
        }
        return null;
    }

    /**
     * 获取单元格的值  先确定单元格的类型，然后根据类型 取值
     *
     * @param cell 单元格
     * @return
     */
    public static String getCellValue(Cell cell) {
        if (cell == null) return "";
        if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
            return cell.getStringCellValue();
        } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(cell.getBooleanCellValue());
        } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
            return cell.getCellFormula();
        } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            return String.valueOf(cell.getNumericCellValue());
        }
        return "";
    }

    /**
     * 判断一个对象的属性是否都为空，
     *
     * @param obj 对象
     * @return false : 至少有一个属性不为空， true: 该对象的属性全为空
     */
    public static boolean allFieldIsNULL(Object obj) {
        if (null == obj || "".equals(obj)) return true;
        try {
            Field[] declaredFields = obj.getClass().getDeclaredFields();
            for (Field field : declaredFields) { // 循环该类，取出类中的每个属性
                if(field.getName().equals("id")) continue;
                if(field.getName().equals("statisticalTime")) continue;
                field.setAccessible(true);// 把该类中的所有属性设置成 public
                Object object = field.get(obj); // object 是对象中的属性
                if (object instanceof CharSequence) { // 判断对象中的属性的类型，是否都是CharSequence的子类
                    if (!Objects.isNull(object)) { // 如果是他的子类，那么就可以用ObjectUtils.isEmpty进行比较
                        return false;
                    }
                } else { //如果不是那就直接用null判断
                    if (!(null == object || "".equals(object))) {
                        return false;
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return true;//false:不都为空
    }

    public static Double parseDouble(String trim) {
        Double parseDouble = 0.0;
        try {
            parseDouble = Double.parseDouble(trim);
        } catch (NumberFormatException e) {
//            System.out.println("导入excel格式问题:要导入的格式为double, 而你导入了 "+trim);
            parseDouble = 0.0;
        }
        return parseDouble;
    }

    public static Integer parseInteger(String trim) {
        Integer integer = 0;
        try {
            integer = Integer.parseInt(trim);
        } catch (NumberFormatException e) {
//            System.out.println("导入excel格式问题:要导入的格式为integer, 而你导入了"+trim);
            integer = 0;
        }
        return integer;
    }

    /**
     * 模糊查询
     * @param workbook
     * @param sheetName
     * @return 返回一个表index
     */
    public static int getSheetIndex(Workbook workbook, String sheetName) {
        for (int index = 0; index < workbook.getNumberOfSheets(); index++) {
            String sheetNameAll = workbook.getSheetName(index);
            String pattern = ".*" + sheetName + ".*";
            boolean isMatch = Pattern.matches(pattern, sheetNameAll);
            if (isMatch) {
                return index;
            }
        }
        //没有查询到
        return -1;
    }




}